package com.hoangqp.android.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "LOOKDICT";

	// Contacts table name
	private static final String TABLE_BOOKS = "books";
	private static final String TABLE_MY_DICT = "my_dict";

	// Books Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_READ = "is_read";
	private static final String KEY_PATH = "path";

	// MY_DCIT Table columns names
	private static final String MYDICT_ID = "id";
	private static final String MYDICT_WORDS = "words";
	private static final String MYDICT_OFFSET = "off_set";
	private static final String MYDICT_SIZE = "size";
	private static final String MYDICT_BOOK_ID = "book_id";

	private static final String CREATE_MY_DICT_TABLE = "CREATE TABLE "
			+ TABLE_MY_DICT + "(" + MYDICT_ID + " INTEGER PRIMARY KEY,"
			+ MYDICT_WORDS + " TEXT," + MYDICT_OFFSET + " INTEGER,"
			+ MYDICT_SIZE + " INTEGER," + MYDICT_BOOK_ID + " INTEGER" + ")";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_BOOKS + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
				+ KEY_PATH + " TEXT ," + KEY_READ + " INTEGER" + ")";
		db.execSQL(CREATE_CONTACTS_TABLE);
		db.execSQL(CREATE_MY_DICT_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new contact
	public void addBook(Book book) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, book.get_name()); // Book Name
		values.put(KEY_READ, book.is_isRead()); // Book is read
		values.put(KEY_PATH, book.get_path()); // path of book in directory

		// Inserting Row
		db.insert(TABLE_BOOKS, null, values);
		db.close(); // Closing database connection
	}

	// Getting single book
	public Book getBook(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_BOOKS, new String[] { KEY_ID, KEY_NAME,
				KEY_PATH, KEY_READ }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		boolean isRead = checkIsRead(Integer.parseInt(cursor.getString(3)));

		Book contact = new Book(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), isRead, cursor.getString(2));
		// return contact
		return contact;
	}

	public int getBookIdWithPath(String path){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_BOOKS, new String[] { KEY_ID}, KEY_PATH + "=?",
				new String[] { path }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();
		
		int bookId = Integer.parseInt(cursor.getString(0));
		
		return bookId;
	}
	// Getting All Books
	public List<Book> getAllBooks() {
		List<Book> bookList = new ArrayList<Book>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_BOOKS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Book book = new Book();
				book.set_id(Integer.parseInt(cursor.getString(0)));
				book.set_name(cursor.getString(1));
				book.set_path(cursor.getString(2));

				boolean isRead = checkIsRead(Integer.parseInt(cursor
						.getString(3)));
				book.set_isRead(isRead);
				// Adding contact to list
				bookList.add(book);
			} while (cursor.moveToNext());
		}

		// return contact list
		return bookList;
	}

	// Updating single contact
	public int updateBook(Book book) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, book.get_name());
		values.put(KEY_PATH, book.get_path());
		values.put(KEY_READ, book.is_isRead());

		// updating row
		return db.update(TABLE_BOOKS, values, KEY_ID + " = ?",
				new String[] { String.valueOf(book.get_id()) });
	}

	// Deleting single contact
	public void deleteBook(Book book) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_BOOKS, KEY_ID + " = ?",
				new String[] { String.valueOf(book.get_id()) });
		db.close();
	}

	// Getting contacts Count
	public int getBooksCount() {
		String countQuery = "SELECT  * FROM " + TABLE_BOOKS;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		int countBook = cursor.getCount();
		cursor.close();

		// return count
		return countBook;
	}

	boolean checkIsRead(int read) {
		if (read == 1)
			return true;
		else
			return false;
	}

	/**
	 * TABLE MYDICT
	 */
	public List<MyDict> getAllMyDict(int bookId) {
		List<MyDict> allMyDict = new ArrayList<MyDict>();
		// Select All Query
		//String selectQuery = "SELECT  * FROM " + TABLE_MY_DICT + "WHERE ";

		SQLiteDatabase db = this.getWritableDatabase();
		//Cursor cursor = db.rawQuery(selectQuery, null);
		Cursor cursor = db.query(TABLE_MY_DICT, new String[] { MYDICT_ID,
				MYDICT_WORDS, MYDICT_OFFSET, MYDICT_SIZE, MYDICT_BOOK_ID },
				MYDICT_BOOK_ID + "=?", new String[] { String.valueOf(bookId) },
				null, null, null);
		
		//loop all my dict
		if(cursor.moveToFirst()){
			do {
				MyDict myDict = new MyDict();
				myDict.set_id(Integer.parseInt(cursor.getString(0)));
				myDict.set_wordEntry(cursor.getString(1));
				myDict.set_offSet(Long.parseLong(cursor.getString(2)));
				myDict.set_size(Long.parseLong(cursor.getString(3)));
				myDict.set_bookId(Integer.parseInt(cursor.getString(4)));
				
				allMyDict.add(myDict);
			} while(cursor.moveToNext());
		}
		
		// return list
		return allMyDict;
	}
	
	public void addDictToMyDict(MyDict myDict){
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(MYDICT_WORDS, myDict.get_wordEntry());
		values.put(MYDICT_OFFSET, myDict.get_offSet());
		values.put(MYDICT_SIZE, myDict.get_size());
		values.put(MYDICT_BOOK_ID, myDict.get_bookId());
		
		db.insert(TABLE_MY_DICT, null, values);
		db.close();
	}
}
